#!/bin/bash

APP=gmall

# 判断日期参数，日期参数是第二个参数
if [ -n "$2" ]; then
    do_date=$2
else
    echo "请传入日期参数"
    exit
fi

common_sql="use ${APP};set hive.exec.dynamic.partition.mode=nonstrict;"

dwd_trade_cart_add_inc_sql="
    with cart as (
        select
            data.id,
            data.user_id,
            data.sku_id,
            date_format(data.create_time, 'yyyy-MM-dd') date_id,
            data.create_time,
            data.source_id,
            data.source_type source_type_code,
            data.sku_num
        from ods_cart_info_inc
        where dt = '${do_date}' and type = 'bootstrap-insert'
    ), source_info as (
        select
            dic_code,
            dic_name source_type_name
        from ods_base_dic_full
        where dt = '${do_date}' and parent_code = '24'
    )
    insert overwrite table dwd_trade_cart_add_inc partition (dt)
    select
        id,
        user_id,
        sku_id,
        date_id,
        create_time,
        source_id,
        source_type_code,
        source_type_name,
        sku_num,
        date_id dt
    from cart
    left join source_info on cart.source_type_code = source_info.dic_code;
"

dwd_trade_order_detail_inc_sql="
    with order_detail as (
        select
            data.id,
            data.order_id,
            data.sku_id,
            data.sku_num,
            date_format(data.create_time, 'yyyy-MM-dd') date_id,
            data.create_time,
            data.source_id,
            data.source_type source_type_code,
            data.order_price * data.sku_num split_original_amount,
            data.split_activity_amount,
            data.split_coupon_amount,
            data.split_total_amount
        from ods_order_detail_inc
        where dt = '${do_date}' and type = 'bootstrap-insert'
    ), \`order\` as (
        select
            data.id,
            data.user_id,
            data.province_id
        from ods_order_info_inc
        where dt = '${do_date}' and type = 'bootstrap-insert'
    ), order_activity as (
        select
            order_detail_id,
            activity_id,
            activity_rule_id
        from (
                 select
                     data.order_detail_id,
                     data.activity_id,
                     data.activity_rule_id,
                     row_number() over (partition by data.order_detail_id order by data.create_time) rk
                 from ods_order_detail_activity_inc
                 where dt = '${do_date}' and type = 'bootstrap-insert'
             ) t where t.rk = 1
    ), order_coupon as (
        select
            order_detail_id,
            coupon_id
        from (
                 select
                     data.order_detail_id,
                     data.coupon_id,
                     row_number() over (partition by data.order_detail_id order by data.create_time) rk
                 from ods_order_detail_coupon_inc
                 where dt = '${do_date}' and type = 'bootstrap-insert'
             ) t where t.rk = 1
    ), source_info as (
        select
            dic_code,
            dic_name source_type_name
        from ods_base_dic_full
        where dt = '${do_date}' and parent_code = '24'
    )
    insert overwrite table dwd_trade_order_detail_inc partition (dt)
    select
        order_detail.id,
        order_id,
        user_id,
        sku_id,
        province_id,
        activity_id,
        activity_rule_id,
        coupon_id,
        date_id,
        create_time,
        source_id,
        source_type_code,
        source_type_name,
        sku_num,
        split_original_amount,
        split_total_amount,
        split_activity_amount,
        split_coupon_amount,
        date_id dt
    from order_detail
    left join \`order\` on order_detail.order_id = \`order\`.id
    left join order_activity on order_activity.order_detail_id = order_detail.id
    left join order_coupon on order_coupon.order_detail_id = order_detail.id
    left join source_info on source_info.dic_code = order_detail.source_type_code;
"

dwd_trade_pay_detail_suc_inc_sql="
    with order_detail as (
        select
            data.id,
            data.order_id,
            data.sku_id,
            data.sku_num,
            data.source_id,
            data.source_type source_type_code,
            data.order_price * data.sku_num split_original_amount,
            data.split_activity_amount,
            data.split_coupon_amount,
            data.split_total_amount split_payment_amount
        from ods_order_detail_inc
        where dt = '${do_date}' and type = 'bootstrap-insert'
    ), \`order\` as (
        select
            data.id,
            data.user_id,
            data.province_id
        from ods_order_info_inc
        where dt = '${do_date}' and type = 'bootstrap-insert'
    ), order_activity as (
        select
            order_detail_id,
            activity_id,
            activity_rule_id
        from (
                 select
                     data.order_detail_id,
                     data.activity_id,
                     data.activity_rule_id,
                     row_number() over (partition by data.order_detail_id order by data.create_time) rk
                 from ods_order_detail_activity_inc
                 where dt = '${do_date}' and type = 'bootstrap-insert'
             ) t where t.rk = 1
    ), order_coupon as (
        select
            order_detail_id,
            coupon_id
        from (
                 select
                     data.order_detail_id,
                     data.coupon_id,
                     row_number() over (partition by data.order_detail_id order by data.create_time) rk
                 from ods_order_detail_coupon_inc
                 where dt = '${do_date}' and type = 'bootstrap-insert'
             ) t where t.rk = 1
    ), payment as (
        select
            data.order_id,
            data.payment_type payment_type_code,
            date_format(data.callback_time, 'yyyy-MM-dd') date_id,
            data.callback_time
        from ods_payment_info_inc
        where dt = '${do_date}' and type = 'bootstrap-insert' and data.callback_time is not null
    ), source_info as (
        select
            dic_code,
            dic_name source_type_name
        from ods_base_dic_full
        where dt = '${do_date}' and parent_code = '24'
    ), pay_way_info as (
        select
            dic_code,
            dic_name payment_type_name
        from ods_base_dic_full
        where dt = '${do_date}' and parent_code = '11'
    )
    insert overwrite table dwd_trade_pay_detail_suc_inc partition (dt)
    select
        order_detail.id,
        order_detail.order_id,
        user_id,
        sku_id,
        province_id,
        activity_id,
        activity_rule_id,
        coupon_id,
        payment_type_code,
        payment_type_name,
        date_id,
        callback_time,
        source_id,
        source_type_code,
        source_type_name,
        sku_num,
        split_original_amount,
        split_activity_amount,
        split_coupon_amount,
        split_payment_amount,
        date_id dt
    from order_detail
    left join \`order\` on order_detail.order_id = \`order\`.id
    left join order_activity on order_detail.id = order_activity.order_detail_id
    left join order_coupon on order_detail.id = order_coupon.order_detail_id
    inner join payment on payment.order_id = \`order\`.id
    left join source_info on order_detail.source_type_code = source_info.dic_code
    left join pay_way_info on payment.payment_type_code = pay_way_info.dic_code;
"

dwd_trade_cart_full_sql="
    insert overwrite table dwd_trade_cart_full partition (dt='${do_date}')
    select
        id,
        user_id,
        sku_id,
        sku_name,
        sku_num
    from ods_cart_info_full
    where dt = '${do_date}' and is_ordered = '0';
"

dwd_trade_trade_flow_acc_sql="
    with order_info as (
        select
            data.id order_id,
            data.user_id,
            data.province_id,
            date_format(data.create_time, 'yyyy-MM-dd') order_date_id,
            data.create_time order_time,
            data.total_amount order_total_amount,
            data.activity_reduce_amount order_activity_amount,
            data.coupon_reduce_amount order_coupon_amount,
            data.original_total_amount order_original_amount
        from ods_order_info_inc
        where type = 'bootstrap-insert' and dt = '${do_date}'
    ), payment_info as (
        select
            data.order_id,
            date_format(data.callback_time, 'yyyy-MM-dd') payment_date_id,
            data.callback_time payment_time,
            data.total_amount payment_amount
        from ods_payment_info_inc
        where type = 'bootstrap-insert' and dt = '${do_date}' and data.callback_time is not null
    ), log_info as (
        select
            data.order_id,
            date_format(data.operate_time, 'yyyy-MM-dd') finish_date_id,
            data.operate_time finish_time
        from ods_order_status_log_inc
        where type = 'bootstrap-insert' and dt = '${do_date}' and (data.order_status  = '1003' or data.order_status = '1004')
    )
    insert overwrite table dwd_trade_trade_flow_acc partition (dt)
    select
        order_info.order_id,
        user_id,
        province_id,
        order_date_id,
        order_time,
        payment_date_id,
        payment_time,
        finish_date_id,
        finish_time,
        order_original_amount,
        order_activity_amount,
        order_coupon_amount,
        order_total_amount,
        payment_amount,
        if (finish_time is null, '9999-12-31', finish_date_id) dt
    from order_info
    left join payment_info on order_info.order_id = payment_info.order_id
    left join log_info on order_info.order_id = log_info.order_id;
"

dwd_tool_coupon_used_inc_sql="
    insert overwrite table dwd_tool_coupon_used_inc partition (dt)
    select
        data.id,
        data.coupon_id,
        data.user_id,
        date_format(data.used_time, 'yyyy-MM-dd') date_id,
        data.used_time,
        date_format(data.used_time, 'yyyy-MM-dd') dt
    from ods_coupon_use_inc
    where type = 'bootstrap-insert' and dt = '${do_date}' and data.used_time is not null;
"

dwd_interaction_favor_add_inc_sql="
    insert overwrite table dwd_interaction_favor_add_inc partition (dt)
    select
        data.id,
        data.user_id,
        data.sku_id,
        date_format(data.create_time, 'yyyy-MM-dd') date_id,
        data.create_time,
        date_format(data.create_time, 'yyyy-MM-dd') dt
    from ods_favor_info_inc
    where type = 'bootstrap-insert' and dt = '${do_date}' and data.is_cancel = 0;
"

dwd_traffic_page_view_inc_sql="
    with view_log as (
        select
            common.ar,
            common.ba brand,
            common.ch channel,
            common.is_new,
            common.md model,
            common.mid mid_id,
            common.os operate_system,
            common.uid user_id,
            common.vc version_code,
            page.item page_item,
            page.item_type page_item_type,
            page.last_page_id,
            page.page_id,
            page.sourceType source_type,
            date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd') date_id,
            date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') view_time,
            page.during_time,
            if (page.last_page_id is null, concat(common.mid, ts), null) session_start
        from ods_log_inc
        where dt = '${do_date}'
            and page.page_id is not null
    ), province_info as (
        select
            id province_id,
            area_code
        from ods_base_province_full
        where dt = '${do_date}'
    ), view_log_with_session as (
        select
            ar,
            brand,
            channel,
            is_new,
            model,
            mid_id,
            operate_system,
            user_id,
            version_code,
            page_item,
            page_item_type,
            last_page_id,
            page_id,
            source_type,
            date_id,
            view_time,
            during_time,
            last_value(session_start, true) over (partition by user_id order by view_time) session_id
        from view_log
    )
    insert overwrite table dwd_traffic_page_view_inc partition (dt = '${do_date}')
    select
        province_id,
        brand,
        channel,
        is_new,
        model,
        mid_id,
        operate_system,
        user_id,
        version_code,
        page_item,
        page_item_type,
        last_page_id,
        page_id,
        source_type,
        date_id,
        view_time,
        session_id,
        during_time
    from view_log_with_session
    left join province_info on view_log_with_session.ar = province_info.area_code;
"

dwd_user_register_inc_sql="
    with user_info as (
        select
            data.id user_id,
            date_format(data.create_time, 'yyyy-MM-dd') date_id,
            data.create_time
        from ods_user_info_inc
        where type = 'bootstrap-insert' and dt = '${do_date}'
    ), log_info as (
        select
            common.ar,
            common.ba brand,
            common.ch channel,
            common.md model,
            common.mid mid_id,
            common.os operate_system,
            common.uid user_id,
            common.vc version_code
        from ods_log_inc
        where dt = '${do_date}' and common.uid is not null and page.page_id = 'register'
    ), region_info as (
        select
            id province_id,
            area_code
        from dim_province_full
        where dt = '${do_date}'
    )
    insert overwrite table dwd_user_register_inc partition (dt)
    select
        user_info.user_id,
        date_id,
        create_time,
        channel,
        province_id,
        version_code,
        mid_id,
        brand,
        model,
        operate_system,
        date_id dt
    from user_info
    left join log_info on log_info.user_id = user_info.user_id
    left join region_info on log_info.ar = region_info.area_code;
"

dwd_user_login_inc_sql="
    with log_info as (
        select
            ar,
            brand,
            channel,
            model,
            mid_id,
            operate_system,
            user_id,
            version_code,
            date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd') date_id,
            date_format(from_utc_timestamp(ts, 'GMT+8'), 'yyyy-MM-dd HH:mm:ss') login_time
        from (
            select
                ar,
                brand,
                channel,
                model,
                mid_id,
                operate_system,
                user_id,
                version_code,
                ts,
                row_number() over (partition by session_id order by ts) rk
            from (
                select
                    ar,
                    brand,
                    channel,
                    model,
                    mid_id,
                    operate_system,
                    user_id,
                    version_code,
                    ts,
                    last_value(session_start, true) over (partition by mid_id order by ts) session_id
                from (
                    select
                        common.ar,
                        common.ba brand,
                        common.ch channel,
                        common.md model,
                        common.mid mid_id,
                        common.os operate_system,
                        common.uid user_id,
                        common.vc version_code,
                        ts,
                        if (page.last_page_id is null, concat(common.mid, ts), null) session_start
                    from ods_log_inc
                    where dt = '${do_date}'
                ) t1
                where t1.user_id is not null
            ) t2
        ) t3
        where t3.rk = 1
    ), region_info as (
        select
            id province_id,
            area_code
        from dim_province_full
        where dt = '${do_date}'
    )
    insert overwrite table dwd_user_login_inc partition (dt = '${do_date}')
    select
        user_id,
        date_id,
        login_time,
        channel,
        province_id,
        version_code,
        mid_id,
        brand,
        model,
        operate_system
    from log_info
    left join region_info on log_info.ar = region_info.area_code;
"

# 判断表名参数，表名参数是第一个参数
case $1 in
"dwd_trade_cart_add_inc")
    hive -e "${common_sql}${dwd_trade_cart_add_inc_sql}"
;;
"dwd_trade_order_detail_inc")
    hive -e "${common_sql}${dwd_trade_order_detail_inc_sql}"
;;
"dwd_trade_pay_detail_suc_inc")
    hive -e "${common_sql}${dwd_trade_pay_detail_suc_inc_sql}"
;;
"dwd_trade_cart_full")
    hive -e "${common_sql}${dwd_trade_cart_full_sql}"
;;
"dwd_trade_trade_flow_acc")
    hive -e "${common_sql}${dwd_trade_trade_flow_acc_sql}"
;;
"dwd_tool_coupon_used_inc")
    hive -e "${common_sql}${dwd_tool_coupon_used_inc_sql}"
;;
"dwd_interaction_favor_add_inc")
    hive -e "${common_sql}${dwd_interaction_favor_add_inc_sql}"
;;
"dwd_traffic_page_view_inc")
    hive -e "${common_sql}${dwd_traffic_page_view_inc_sql}"
;;
"dwd_user_register_inc")
    hive -e "${common_sql}${dwd_user_register_inc_sql}"
;;
"dwd_user_login_inc")
    hive -e "${common_sql}${dwd_user_login_inc_sql}"
;;
"all")
    hive -e "${common_sql}${dwd_trade_cart_add_inc_sql}${dwd_trade_order_detail_inc_sql}${dwd_trade_pay_detail_suc_inc_sql}${dwd_trade_cart_full_sql}${dwd_trade_trade_flow_acc_sql}${dwd_tool_coupon_used_inc_sql}${dwd_interaction_favor_add_inc_sql}${dwd_traffic_page_view_inc_sql}${dwd_user_register_inc_sql}${dwd_user_login_inc_sql}"
;;
esac